package org.niit.util

import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet}
import java.util.Properties

/**
 * JDBC工具类，用于数据库连接和操作
 */
object JDBCUtil {
  
  // 获取数据库连接
  def getConnection(): Connection = {
    val properties = ConfigUtil.getJdbcProperties()
    Class.forName("com.mysql.jdbc.Driver")
    DriverManager.getConnection(ConfigUtil.jdbcUrl, properties)
  }
  
  // 关闭资源
  def close(connection: Connection = null, statement: PreparedStatement = null, resultSet: ResultSet = null): Unit = {
    try {
      if (resultSet != null) resultSet.close()
      if (statement != null) statement.close()
      if (connection != null) connection.close()
    } catch {
      case e: Exception => e.printStackTrace()
    }
  }
  
  /**
   * 执行查询操作
   *
   * @param sql SQL查询语句
   * @param params 查询参数
   * @return 查询结果集
   */
  def executeQuery(sql: String, params: Array[Any]): ResultSet = {
    val connection = getConnection()
    val statement = connection.prepareStatement(sql)
    
    // 设置参数
    if (params != null && params.length > 0) {
      for (i <- 0 until params.length) {
        statement.setObject(i + 1, params(i))
      }
    }
    
    val resultSet = statement.executeQuery()
    // 注意：这里不能关闭连接和语句，因为ResultSet依赖于它们
    // 调用方需要负责在使用完ResultSet后关闭它
    resultSet
  }
  
  // 执行更新操作
  def executeUpdate(sql: String, params: Array[Any]): Int = {
    var connection: Connection = null
    var statement: PreparedStatement = null
    try {
      connection = getConnection()
      statement = connection.prepareStatement(sql)
      
      // 设置参数
      if (params != null && params.length > 0) {
        for (i <- 0 until params.length) {
          statement.setObject(i + 1, params(i))
        }
      }
      
      statement.executeUpdate()
    } catch {
      case e: Exception =>
        e.printStackTrace()
        0
    } finally {
      close(connection, statement)
    }
  }
  
  // 执行批量更新操作
  def executeBatchUpdate(sql: String, paramsList: List[Array[Any]]): Array[Int] = {
    var connection: Connection = null
    var statement: PreparedStatement = null
    try {
      connection = getConnection()
      connection.setAutoCommit(false)
      statement = connection.prepareStatement(sql)
      
      for (params <- paramsList) {
        if (params != null && params.length > 0) {
          for (i <- 0 until params.length) {
            statement.setObject(i + 1, params(i))
          }
        }
        statement.addBatch()
      }
      
      val result = statement.executeBatch()
      connection.commit()
      result
    } catch {
      case e: Exception =>
        e.printStackTrace()
        if (connection != null) {
          connection.rollback()
        }
        Array.empty[Int]
    } finally {
      if (connection != null) {
        connection.setAutoCommit(true)
      }
      close(connection, statement)
    }
  }
  
  /**
   * 清空表数据
   *
   * @param tableName 表名
   * @return 操作结果
   */
  def truncateTable(tableName: String): Int = {
    val sql = s"TRUNCATE TABLE $tableName"
    executeUpdate(sql, Array())
  }
  
  /**
   * 判断一条数据是否存在
   *
   * @param sql SQL查询语句
   * @param params 查询参数
   * @return 是否存在
   */
  def isExist(sql: String, params: Array[Any]): Boolean = {
    var connection: Connection = null
    var statement: PreparedStatement = null
    var resultSet: ResultSet = null
    var flag: Boolean = false
    
    try {
      connection = getConnection()
      statement = connection.prepareStatement(sql)
      
      if (params != null && params.length > 0) {
        for (i <- 0 until params.length) {
          statement.setObject(i + 1, params(i))
        }
      }
      
      resultSet = statement.executeQuery()
      flag = resultSet.next()
    } catch {
      case e: Exception => 
        e.printStackTrace()
        false
    } finally {
      close(connection, statement, resultSet)
    }
    
    flag
  }
  
  /**
   * 获取MySQL的一条数据
   *
   * @param sql SQL查询语句
   * @param params 查询参数
   * @return 查询结果
   */
  def getDataFromMysql(sql: String, params: Array[Any]): Long = {
    var connection: Connection = null
    var statement: PreparedStatement = null
    var resultSet: ResultSet = null
    var result: Long = 0L
    
    try {
      connection = getConnection()
      statement = connection.prepareStatement(sql)
      
      if (params != null && params.length > 0) {
        for (i <- 0 until params.length) {
          statement.setObject(i + 1, params(i))
        }
      }
      
      resultSet = statement.executeQuery()
      if (resultSet.next()) {
        result = resultSet.getLong(1)
      }
    } catch {
      case e: Exception => 
        e.printStackTrace()
        0L
    } finally {
      close(connection, statement, resultSet)
    }
    
    result
  }
  
  /**
   * 创建表如果表不存在
   * 
   * @param tableName 表名
   * @param tableSchema 表结构定义
   */
  def createTableIfNotExists(tableName: String, tableSchema: String): Unit = {
    val sql = s"CREATE TABLE IF NOT EXISTS $tableName ($tableSchema)"
    executeUpdate(sql, Array())
  }
} 